//
//  DataBaseHandle.m
//  豆瓣HW
//
//  Created by dllo on 15/12/3.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Activity.h"
#import "Movie.h"
#import "User.h"

@implementation DataBaseHandle

+ (instancetype)shareDataBase
{
    static DataBaseHandle *dataBase = nil;
    if (nil == dataBase) {
        dataBase = [[DataBaseHandle alloc] init];
    }
    return dataBase;
}

static sqlite3 *DB = nil;

- (void)openDB
{
    // 获取路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *filePath = [path stringByAppendingPathComponent:@"dataBase.db"];
    
    int ret = sqlite3_open(filePath.UTF8String, &DB);
    
    if (SQLITE_OK == ret) {
        NSLog(@"打开数据库成功!");
    } else {
        NSLog(@"打开数据库失败!");
    }
  
}

- (void)closeDB
{
    int ret = sqlite3_close(DB);
    if (SQLITE_OK == ret) {
        NSLog(@"关闭数据库成功!");
    } else {
        NSLog(@"关闭数据库失败!");
    }

}

- (void)createUserTable
{
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS user(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, password TEXT, state TEXT)";
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"创建表单成功!");
    } else {
        NSLog(@"创建表单失败!");
    }
  
}

- (void)createActivityTable
{
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS activity(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, title TEXT)";

    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == ret) {
        NSLog(@"创建表单成功!");
    } else {
        NSLog(@"创建表单失败!");
    }

}

- (void)createMovieTable
{
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS movie(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, title TEXT)";
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == ret) {
        NSLog(@"创建表单成功!");
    } else {
        NSLog(@"创建表单失败!");
    }
}

- (void)deleteUserTable
{
    NSString *sqlStr = @"DROP TABLE user";
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"删除表单成功!");
    } else {
        NSLog(@"删除表单失败!");
    }
    
}

- (void)deleteActivityDataWithUserName:(User *)user
{
    NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM activity WHERE name = '%@'", user.name];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"删除表单成功!");
    } else {
        NSLog(@"删除表单失败!");
    }
    
}

- (void)deleteMovieDataWithUserName:(User *)user
{
        NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM movie WHERE name = '%@'", user.name];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"删除表单成功!");
    } else {
        NSLog(@"删除表单失败!");
    }
    
}

- (void)insertDataWithUser:(User *)user
{
    NSString *sqlStr = [NSString stringWithFormat:@"INSERT INTO user(name, password, state) VALUES ('%@', '%@', '%@')", user.name, user.password, user.state];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"插入数据成功!");
    } else {
        NSLog(@"插入数据失败!");
    }
    
}

- (void)insertDataWithActivity:(User *)user ActivityName:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"INSERT INTO activity(name, title) VALUES ('%@', '%@')", user.name, title];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"插入数据成功!");
    } else {
        NSLog(@"插入数据失败!");
    }
    
}

- (void)insertDataWithMovie:(User *)user MovieName:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"INSERT INTO movie(name, title) VALUES ('%@', '%@')", user.name, title];
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"插入数据成功");
    } else {
        NSLog(@"插入数据失败!");
    }

}

- (void)deleteDataWithUserName:(NSString *)name
{
    NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM user WHERE name = '%@'", name];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"删除数据成功!");
    } else {
        NSLog(@"删除数据失败!");
    }

}

- (void)deleteDataWithActivityTitle:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM activity WHERE title = '%@'", title];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"删除数据成功!");
    } else {
        NSLog(@"删除数据失败!");
    }


}

- (void)deleteDataWithMovieTitle:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM movie WHERE title = '%@'", title];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"删除数据成功!");
    } else {
        NSLog(@"删除数据失败!");
    }
}

- (void)updateDataWithUser:(User *)user
{
    NSString *sqlStr = [NSString stringWithFormat:@"UPDATE user SET name = '%@', password = '%@', state = '%@' WHERE name = '%@'", user.name, user.password, user.state, user.name];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"更新数据成功!");
    } else {
        NSLog(@"更新数据失败!");
    }

}

- (void)updateDataWithActivity:(User *)user ActivityName:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"UPDATE activity SET name = '%@', title = '%@'", user.name, title];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"更新数据成功!");
    } else {
        NSLog(@"更新数据失败!");
    }
}

- (void)updateDataWithMovie:(User *)user MovieName:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"UPDATE movie SET name = '%@', title = '%@'", user.name, title];
    
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == ret) {
        NSLog(@"更新数据成功!");
    } else {
        NSLog(@"更新数据失败!");
    }
}

- (NSMutableArray *)selectAllUser
{
    NSString *sqlStr = @" SELECT * FROM user";
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        NSMutableArray *userArr = [NSMutableArray array];
        
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            const unsigned char *state = sqlite3_column_text(stmt, 3);
            
            User *user = [[User alloc] init];
            user.name = [NSString stringWithUTF8String:(const char *)name];
            user.password = [NSString stringWithUTF8String:(const char *)password];
            user.state = [NSString stringWithUTF8String:(const char *)state];
            [userArr addObject:user];
            [user release];
        }
        return userArr;
        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }

}

- (User *)selectByName:(NSString *)name
{
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM user WHERE  name = '%@'", name];
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        User *user = [[[User alloc] init] autorelease];
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            
            user.name = [NSString stringWithUTF8String:(const char *)name];
            user.password = [NSString stringWithUTF8String:(const char *)password];
            
        }
        return user;
        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }
    
}

- (User *)selectByState:(NSString *)state
{
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM user WHERE  state = '%@'", state];
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        User *user = [[[User alloc] init] autorelease];
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            const unsigned char *state = sqlite3_column_text(stmt, 3);
            
            user.name = [NSString stringWithUTF8String:(const char *)name];
            user.password = [NSString stringWithUTF8String:(const char *)password];
            user.state = [NSString stringWithUTF8String:(const char *)state];
            
        }
        return user;

        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }


}

- (NSMutableArray *)selectByActivityTitle:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM activity WHERE  title = '%@'", title];
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        NSMutableArray *arr = [NSMutableArray array];
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            
            NSString *str = [NSString stringWithUTF8String:(const char *)name];
            [arr addObject:str];
        }
        return arr;
        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }
    
    
}

- (NSMutableArray *)selectByMovieTitle:(NSString *)title
{
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM movie WHERE  title = '%@'", title];
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        NSMutableArray *arr = [NSMutableArray array];
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            
            NSString *str = [NSString stringWithUTF8String:(const char *)name];
            [arr addObject:str];
        }
        return arr;
        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }
    
    
}

- (NSMutableArray *)selectActivityByName:(NSString *)name
{
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM activity WHERE  name = '%@'", name];
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        NSMutableArray *arr = [NSMutableArray array];
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            
            NSString *str = [NSString stringWithUTF8String:(const char *)title];
            [arr addObject:str];
        }
        return arr;
        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }
    
    
}

- (NSMutableArray *)selectMovieByName:(NSString *)name
{
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM movie WHERE  name = '%@'", name];
    
    sqlite3_stmt *stmt = nil;
    
    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == ret) {
        NSMutableArray *arr = [NSMutableArray array];
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            
            NSString *str = [NSString stringWithUTF8String:(const char *)title];
            [arr addObject:str];
        }
        return arr;
        
    } else {
        NSLog(@"获取数据失败!");
        return nil;
    }
    
    
}

//- (NSMutableArray *)selectActivityByName:(NSString *)name
//{
//    NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM activity WHERE  name = '%@'", name];
//    
//    sqlite3_stmt *stmt = nil;
//    
//    int ret = sqlite3_prepare_v2(DB, sqlStr.UTF8String, -1, &stmt, NULL);
//    
//    if (SQLITE_OK == ret) {
//        NSMutableArray *titleArr = [NSMutableArray array];
//        while (SQLITE_ROW == sqlite3_step(stmt)) {
//            const unsigned char *name = sqlite3_column_text(stmt, 1);
//            const unsigned char *title = sqlite3_column_text(stmt, 2);
//            
//            [titleArr addObject:title];
//            [title release];
//            
//        }
//    }
//    
//    
//}


@end
